﻿using System;
using System.IO;
using NPOI.Util;
using System.Drawing;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.HSSF.Record;
using System.Data;
using SEO.Business;
using System.Web;
using System.Text;
using SEO.Entity;
/// <summary>
/// 生成Excel
/// </summary>
public partial class CreateExcel : AdminPage
{
    protected string strType = string.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            InitData();
        }
    }

    /// <summary>
    /// 初始化
    /// </summary>
    protected void InitData()
    {
        string strType = Common.GetQueryValue("type", 0);
        switch (strType)
        {
            //生成关键字排名对比Excel
            case "keywordsreport":
                CreateKeywordsReport();
                break;
            //生成用户文章报表Excel
            case "userarticlereport":
                CreateUserArticleReport();
                break;
            case "userarticlereportlist":
                CreateUserArticleReportList();
                break;
        }


    }

    #region 生成关键字排名对比Excel
    /// <summary>
    /// 生成关键字排名对比Excel
    /// </summary>
    protected void CreateKeywordsReport()
    {
        if (nAdminFeatures != 1)
        {
            if (!SupportFeatures(AdminFEATURE.KeywordsReport))
            {
                Response.Write("没有权限！");
                Response.End();
            }
        }
        int nCountType = Convert.ToInt32(Common.GetQueryValue("counttype", 0));
        DateTime dBeginTime = Convert.ToDateTime(Common.GetQueryValue("datetime", 0));
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(dBeginTime.ToString("yyyy-MM-dd"));

        //样式1
        Color titlBgColor = Color.FromArgb(204, 255, 204);
        Color titlFontColor = Color.FromArgb(0, 128, 0);
        HSSFFont font = workbook.CreateFont();
        font.FontHeight = 11 * 20;
        font.FontName = "宋体";
        font.Color = GetXLColour(workbook, titlFontColor);
        HSSFCellStyle style = workbook.CreateCellStyle();
        style.SetFont(font);
        style.BorderBottom = CellBorderType.THIN;
        style.BorderLeft = CellBorderType.THIN;
        style.BorderRight = CellBorderType.THIN;
        style.BorderTop = CellBorderType.THIN;
        style.VerticalAlignment = CellVerticalAlignment.CENTER;
        style.Alignment = CellHorizontalAlignment.CENTER;
        style.FillForegroundColor = GetXLColour(workbook, titlBgColor);
        style.FillPattern = CellFillPattern.ALT_BARS;
        style.FillBackgroundColor = GetXLColour(workbook, titlBgColor);

        //样式2
        Color titlBgColor1 = Color.FromArgb(51, 204, 204);
        Color titlFontColor1 = Color.FromArgb(128, 0, 128);
        HSSFFont font1 = workbook.CreateFont();
        font1.FontHeight = 11 * 20;
        font1.FontName = "宋体";
        font1.Color = GetXLColour(workbook, titlFontColor1);
        HSSFCellStyle style1 = workbook.CreateCellStyle();
        style1.SetFont(font1);
        style1.BorderBottom = CellBorderType.THIN;
        style1.BorderLeft = CellBorderType.THIN;
        style1.BorderRight = CellBorderType.THIN;
        style1.BorderTop = CellBorderType.THIN;
        style1.VerticalAlignment = CellVerticalAlignment.CENTER;
        style1.Alignment = CellHorizontalAlignment.CENTER;
        style1.FillForegroundColor = GetXLColour(workbook, titlBgColor1);
        style1.FillPattern = CellFillPattern.ALT_BARS;
        style1.FillBackgroundColor = GetXLColour(workbook, titlBgColor1);

        //样式3
        Color titlBgColor2 = Color.FromArgb(255, 128, 128);
        Color titlFontColor2 = Color.FromArgb(153, 51, 0);
        HSSFFont font2 = workbook.CreateFont();
        font2.FontHeight = 11 * 20;
        font2.FontName = "宋体";
        font2.Color = GetXLColour(workbook, titlFontColor2);
        HSSFCellStyle style2 = workbook.CreateCellStyle();
        style2.SetFont(font2);
        style2.BorderBottom = CellBorderType.THIN;
        style2.BorderLeft = CellBorderType.THIN;
        style2.BorderRight = CellBorderType.THIN;
        style2.BorderTop = CellBorderType.THIN;
        style2.VerticalAlignment = CellVerticalAlignment.CENTER;
        style2.Alignment = CellHorizontalAlignment.CENTER;
        style2.FillForegroundColor = GetXLColour(workbook, titlBgColor2);
        style2.FillPattern = CellFillPattern.ALT_BARS;
        style2.FillBackgroundColor = GetXLColour(workbook, titlBgColor2);

        //样式4
        HSSFFont font3 = workbook.CreateFont();
        font3.FontHeight = 11 * 20;
        font3.FontName = "宋体";
        HSSFCellStyle style3 = workbook.CreateCellStyle();
        style3.SetFont(font3);
        style3.BorderBottom = CellBorderType.THIN;
        style3.BorderLeft = CellBorderType.THIN;
        style3.BorderRight = CellBorderType.THIN;
        style3.BorderTop = CellBorderType.THIN;
        style3.VerticalAlignment = CellVerticalAlignment.CENTER;
        style3.Alignment = CellHorizontalAlignment.CENTER;

        HSSFRow rowTitle = sheet.CreateRow(0);
        rowTitle.Height = 20 * 20;
        HSSFCell cellTitle = rowTitle.CreateCell(0);
        cellTitle.CellStyle = style2;
        cellTitle.SetCellValue("关键词");
        sheet.SetColumnWidth(0, 60 * 256);

        //获取所有关键字
        DataTable dtKeywords = new BisKeywords().GetAllList1();
        for (int i = 0; i < dtKeywords.Rows.Count; i++)
        {
            HSSFRow rowKey = sheet.CreateRow(i + 1);
            rowKey.Height = 20 * 20;
            HSSFCell cellKey = rowKey.CreateCell(0);
            cellKey.CellStyle = style1;
            cellKey.SetCellValue(dtKeywords.Rows[i]["Name"].ToString());
        }


        //医院信息
        DataTable dtMedical = new BisMedical().GetList();
        for (int i = 0; i < dtMedical.Rows.Count; i++)
        {
            HSSFCell cellMedical = rowTitle.CreateCell(i + 1);
            cellMedical.CellStyle = style;
            cellMedical.SetCellValue(dtMedical.Rows[i]["Name"].ToString());
            sheet.SetColumnWidth(i + 1, 20 * 256);
        }

        //获取报表
        DataTable dtReport = new BisKeywordsReport().GetReportList(dBeginTime, dBeginTime.AddDays(1));

        for (int i = 0; i < dtKeywords.Rows.Count; i++)
        {
            for (int j = 0; j < dtMedical.Rows.Count; j++)
            {
                HSSFCell cellCount = sheet.GetRow(i + 1).CreateCell(j + 1);
                cellCount.CellStyle = style3;
                int nCount = 0;
                DataRow[] drReportList = dtReport.Select("KeywordsID=" + dtKeywords.Rows[i]["ID"].ToString() + " and MedicalID=" + dtMedical.Rows[j]["ID"].ToString());
                if (drReportList.Length > 0)
                {
                    switch (nCountType)
                    {
                        case 0:
                            nCount = Convert.ToInt32(drReportList[0]["FirstCount"]) + Convert.ToInt32(drReportList[0]["SecondCount"]) + Convert.ToInt32(drReportList[0]["ThirdCount"]);
                            break;
                        case 1:
                            nCount = Convert.ToInt32(drReportList[0]["FirstCount"]);
                            break;
                        case 2:
                            nCount = Convert.ToInt32(drReportList[0]["SecondCount"]);
                            break;
                        case 3:
                            nCount = Convert.ToInt32(drReportList[0]["ThirdCount"]);
                            break;
                    }

                }
                cellCount.SetCellValue(nCount.ToString());
            }
        }

        dtReport.Dispose();
        dtKeywords.Dispose();
        dtMedical.Dispose();

        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet.Dispose();
            DownExcel(ms, "k_" + dBeginTime.ToString("yyyyMMdd"));
        }
    }
    #endregion

    #region 生成用户文章报表Excel
    /// <summary>
    /// 生成用户文章报表Excel
    /// </summary>
    protected void CreateUserArticleReport()
    {
        if (nAdminFeatures != 1)
        {
            if (!SupportFeatures(AdminFEATURE.UserArticleReportList))
            {
                Response.Write("没有权限！");
                Response.End();
            }
        }
        int nUserID = Convert.ToInt32(Common.GetQueryValue("userid", 0));
        DateTime dBeginTime = Convert.ToDateTime(Common.GetQueryValue("begintime", 0));
        DateTime dEndTime = Convert.ToDateTime(Common.GetQueryValue("endtime", 0));
        string strName = "所有";
        string strUserName = "all";
        if (nUserID > 0)
        {
            AdminInfo adminInfo = new BisAdminInfo().GetAdmin(nUserID);
            strName = adminInfo.Name;
            strUserName = adminInfo.UserName;
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(dBeginTime.ToString(strName));

        //样式1
        Color titlBgColor = Color.FromArgb(255, 128, 128);
        Color titlFontColor = Color.FromArgb(153, 51, 0);
        HSSFFont font = workbook.CreateFont();
        font.FontHeight = 11 * 20;
        font.FontName = "宋体";
        font.Color = GetXLColour(workbook, titlFontColor);
        HSSFCellStyle style = workbook.CreateCellStyle();
        style.SetFont(font);
        style.BorderBottom = CellBorderType.THIN;
        style.BorderLeft = CellBorderType.THIN;
        style.BorderRight = CellBorderType.THIN;
        style.BorderTop = CellBorderType.THIN;
        style.VerticalAlignment = CellVerticalAlignment.CENTER;
        style.Alignment = CellHorizontalAlignment.CENTER;
        style.FillForegroundColor = GetXLColour(workbook, titlBgColor);
        style.FillPattern = CellFillPattern.ALT_BARS;
        style.FillBackgroundColor = GetXLColour(workbook, titlBgColor);

        //样式2
        Color titlBgColor1 = Color.FromArgb(204, 255, 204);
        Color titlFontColor1 = Color.FromArgb(0, 128, 0);
        HSSFFont font1 = workbook.CreateFont();
        font1.FontHeight = 11 * 20;
        font1.FontName = "宋体";
        font1.Color = GetXLColour(workbook, titlFontColor1);
        HSSFCellStyle style1 = workbook.CreateCellStyle();
        style1.SetFont(font1);
        style1.BorderBottom = CellBorderType.THIN;
        style1.BorderLeft = CellBorderType.THIN;
        style1.BorderRight = CellBorderType.THIN;
        style1.BorderTop = CellBorderType.THIN;
        style1.VerticalAlignment = CellVerticalAlignment.CENTER;
        style1.Alignment = CellHorizontalAlignment.CENTER;
        style1.FillForegroundColor = GetXLColour(workbook, titlBgColor1);
        style1.FillPattern = CellFillPattern.ALT_BARS;
        style1.FillBackgroundColor = GetXLColour(workbook, titlBgColor1);

        //样式3
        Color titlBgColor2 = Color.FromArgb(255, 255, 153);
        Color titlFontColor2 = Color.FromArgb(153, 51, 0);
        HSSFFont font2 = workbook.CreateFont();
        font2.FontHeight = 11 * 20;
        font2.FontName = "宋体";
        font2.Color = GetXLColour(workbook, titlFontColor2);
        HSSFCellStyle style2 = workbook.CreateCellStyle();
        style2.SetFont(font2);
        style2.BorderBottom = CellBorderType.THIN;
        style2.BorderLeft = CellBorderType.THIN;
        style2.BorderRight = CellBorderType.THIN;
        style2.BorderTop = CellBorderType.THIN;
        style2.VerticalAlignment = CellVerticalAlignment.CENTER;
        style2.Alignment = CellHorizontalAlignment.CENTER;
        style2.FillForegroundColor = GetXLColour(workbook, titlBgColor2);
        style2.FillPattern = CellFillPattern.ALT_BARS;
        style2.FillBackgroundColor = GetXLColour(workbook, titlBgColor2);

        //样式4
        HSSFFont font3 = workbook.CreateFont();
        font3.FontHeight = 11 * 20;
        font3.FontName = "宋体";
        HSSFCellStyle style3 = workbook.CreateCellStyle();
        style3.SetFont(font3);
        style3.BorderBottom = CellBorderType.THIN;
        style3.BorderLeft = CellBorderType.THIN;
        style3.BorderRight = CellBorderType.THIN;
        style3.BorderTop = CellBorderType.THIN;
        style3.VerticalAlignment = CellVerticalAlignment.CENTER;
        style3.Alignment = CellHorizontalAlignment.CENTER;

        //姓名
        HSSFRow rowTitle = sheet.CreateRow(0);
        rowTitle.Height = 20 * 20;
        HSSFCell cellTitle = rowTitle.CreateCell(0);
        cellTitle.CellStyle = style;
        cellTitle.SetCellValue("姓名");
        sheet.SetColumnWidth(0, 20 * 256);


        HSSFCell cellName = rowTitle.CreateCell(1);
        cellName.CellStyle = style;
        cellName.SetCellValue(strName);
        for (int i = 2; i < 10; i++)
        {
            rowTitle.CreateCell(i).CellStyle = style;
        }
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 10));

        //日期
        HSSFRow rowTime = sheet.CreateRow(1);
        rowTime.Height = 20 * 20;
        HSSFCell cellTime = rowTime.CreateCell(0);
        cellTime.CellStyle = style1;
        cellTime.SetCellValue("日期");
        sheet.CreateRow(2).CreateCell(0).CellStyle = style1;
        sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));

        //站内
        HSSFCell cellOurWebTitle = sheet.GetRow(1).CreateCell(1);
        cellOurWebTitle.CellStyle = style2;
        cellOurWebTitle.SetCellValue("站内");
        sheet.GetRow(1).CreateCell(2).CellStyle = style2;
        sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 2));
        HSSFCell cellOurWebCount = sheet.GetRow(2).CreateCell(1);
        cellOurWebCount.CellStyle = style2;
        cellOurWebCount.SetCellValue("总篇数");
        HSSFCell cellOurWebUserCount = sheet.GetRow(2).CreateCell(2);
        cellOurWebUserCount.CellStyle = style2;
        cellOurWebUserCount.SetCellValue("收录数");

        //新闻源
        HSSFCell cellNewsWebTitle = sheet.GetRow(1).CreateCell(3);
        cellNewsWebTitle.CellStyle = style2;
        cellNewsWebTitle.SetCellValue("新闻源");
        sheet.GetRow(1).CreateCell(4).CellStyle = style2;
        sheet.GetRow(1).CreateCell(5).CellStyle = style2;
        sheet.GetRow(1).CreateCell(6).CellStyle = style2;
        sheet.AddMergedRegion(new CellRangeAddress(1, 1, 3, 6));
        HSSFCell cellNewsAllCount = sheet.GetRow(2).CreateCell(3);
        cellNewsAllCount.CellStyle = style2;
        cellNewsAllCount.SetCellValue("总篇数");
        HSSFCell cellNewsAllSEOCount = sheet.GetRow(2).CreateCell(4);
        cellNewsAllSEOCount.CellStyle = style2;
        cellNewsAllSEOCount.SetCellValue("收录篇数");
        HSSFCell cellNewsCount1 = sheet.GetRow(2).CreateCell(5);
        cellNewsCount1.CellStyle = style2;
        cellNewsCount1.SetCellValue("首页收录数");
        HSSFCell cellNewsCount2 = sheet.GetRow(2).CreateCell(6);
        cellNewsCount2.CellStyle = style2;
        cellNewsCount2.SetCellValue("第二页收录数");

        //外链
        HSSFCell cellLinkTitle = sheet.GetRow(1).CreateCell(7);
        cellLinkTitle.CellStyle = style2;
        cellLinkTitle.SetCellValue("外链");
        sheet.GetRow(1).CreateCell(8).CellStyle = style2;
        sheet.GetRow(1).CreateCell(9).CellStyle = style2;
        sheet.GetRow(1).CreateCell(10).CellStyle = style2;
        sheet.AddMergedRegion(new CellRangeAddress(1, 1, 7, 10));
        HSSFCell cellLinkAllCount = sheet.GetRow(2).CreateCell(7);
        cellLinkAllCount.CellStyle = style2;
        cellLinkAllCount.SetCellValue("总篇数");
        HSSFCell cellLinkAllSEOCount = sheet.GetRow(2).CreateCell(8);
        cellLinkAllSEOCount.CellStyle = style2;
        cellLinkAllSEOCount.SetCellValue("收录篇数");
        HSSFCell cellLinkCount1 = sheet.GetRow(2).CreateCell(9);
        cellLinkCount1.CellStyle = style2;
        cellLinkCount1.SetCellValue("首页收录数");
        HSSFCell cellLinkCount2 = sheet.GetRow(2).CreateCell(10);
        cellLinkCount2.CellStyle = style2;
        cellLinkCount2.SetCellValue("第二页收录数");


        //获取报表
        DataTable dt = new BisUserArticleReport().GetList(nUserID, dBeginTime, dEndTime);
        DataTable dtNow = dt.Clone();
        int nDay = (dEndTime - dBeginTime).Days;
        int nOurWebCount = 0;
        int nOurWebUserCount = 0;
        int nNewsAllCount = 0;
        int nNewsAllSEOCount = 0;
        int nNewsCount1 = 0;
        int nNewsCount2 = 0;
        int nLinkAllCount = 0;
        int nLinkAllSEOCount = 0;
        int nLinkCount1 = 0;
        int nLinkCount2 = 0;
        if (nDay >= 0)
        {
            for (int i = 0; i <= nDay; i++)
            {
                HSSFRow rowCount = sheet.CreateRow(i + 3);
                for (int j = 0; j < 11; j++)
                {
                    rowCount.CreateCell(j).CellStyle = (j == 0 ? style1 : style3);
                }
                rowCount.GetCell(0).SetCellValue(dBeginTime.AddDays(i).ToString("yyyy年MM月dd日"));
                bool bTag = false;
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr["CreateTime"].ToString() == dBeginTime.AddDays(i).ToString("yyyy-MM-dd"))
                    {
                        rowCount.GetCell(1).SetCellValue(dr["OurWebCount"].ToString());
                        rowCount.GetCell(2).SetCellValue(dr["OurWebUserCount"].ToString());
                        rowCount.GetCell(3).SetCellValue(dr["NewsAllCount"].ToString());
                        rowCount.GetCell(4).SetCellValue(dr["NewsAllSEOCount"].ToString());
                        rowCount.GetCell(5).SetCellValue(dr["NewsCount1"].ToString());
                        rowCount.GetCell(6).SetCellValue(dr["NewsCount2"].ToString());
                        rowCount.GetCell(7).SetCellValue(dr["LinkAllCount"].ToString());
                        rowCount.GetCell(8).SetCellValue(dr["LinkAllSEOCount"].ToString());
                        rowCount.GetCell(9).SetCellValue(dr["LinkCount1"].ToString());
                        rowCount.GetCell(10).SetCellValue(dr["LinkCount2"].ToString());

                        nOurWebCount += Convert.ToInt32(dr["OurWebCount"]);
                        nOurWebUserCount += Convert.ToInt32(dr["OurWebUserCount"]);
                        nNewsAllCount += Convert.ToInt32(dr["NewsAllCount"]);
                        nNewsAllSEOCount += Convert.ToInt32(dr["NewsAllSEOCount"]);
                        nNewsCount1 += Convert.ToInt32(dr["NewsCount1"]);
                        nNewsCount2 += Convert.ToInt32(dr["NewsCount2"]);
                        nLinkAllCount += Convert.ToInt32(dr["LinkAllCount"]);
                        nLinkAllSEOCount += Convert.ToInt32(dr["LinkAllSEOCount"]);
                        nLinkCount1 += Convert.ToInt32(dr["LinkCount1"]);
                        nLinkCount2 += Convert.ToInt32(dr["LinkCount2"]);
                        bTag = true;
                        break;
                    }
                }
                if (!bTag)
                {
                    rowCount.GetCell(1).SetCellValue("0");
                    rowCount.GetCell(2).SetCellValue("0");
                    rowCount.GetCell(3).SetCellValue("0");
                    rowCount.GetCell(4).SetCellValue("0");
                    rowCount.GetCell(5).SetCellValue("0");
                    rowCount.GetCell(6).SetCellValue("0");
                    rowCount.GetCell(7).SetCellValue("0");
                    rowCount.GetCell(8).SetCellValue("0");
                    rowCount.GetCell(9).SetCellValue("0");
                    rowCount.GetCell(10).SetCellValue("0");
                }
            }
        }

        //总计
        HSSFRow rowAllCount = sheet.CreateRow(nDay + 4);
        for (int j = 0; j < 11; j++)
        {
            rowAllCount.CreateCell(j).CellStyle = style;
        }
        rowAllCount.GetCell(0).SetCellValue("总计");
        rowAllCount.GetCell(1).SetCellValue(nOurWebCount.ToString());
        rowAllCount.GetCell(2).SetCellValue(nOurWebUserCount.ToString());
        rowAllCount.GetCell(3).SetCellValue(nNewsAllCount.ToString());
        rowAllCount.GetCell(4).SetCellValue(nNewsAllSEOCount.ToString());
        rowAllCount.GetCell(5).SetCellValue(nNewsCount1.ToString());
        rowAllCount.GetCell(6).SetCellValue(nNewsCount2.ToString());
        rowAllCount.GetCell(7).SetCellValue(nLinkAllCount.ToString());
        rowAllCount.GetCell(8).SetCellValue(nLinkAllSEOCount.ToString());
        rowAllCount.GetCell(9).SetCellValue(nLinkCount1.ToString());
        rowAllCount.GetCell(10).SetCellValue(nLinkCount2.ToString());

        sheet.SetColumnWidth(4, 10 * 256);
        sheet.SetColumnWidth(5, 15 * 256);
        sheet.SetColumnWidth(6, 15 * 256);
        sheet.SetColumnWidth(8, 10 * 256);
        sheet.SetColumnWidth(9, 15 * 256);
        sheet.SetColumnWidth(10, 15 * 256);
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet.Dispose();
            DownExcel(ms, strUserName + "(" + dBeginTime.ToString("yyyyMMdd") + "-" + dEndTime.ToString("yyyyMMdd") + ")");
        }
    }
    #endregion

    #region 生成关键字优化量统计Excel
    /// <summary>
    /// 生成关键字优化量统计Excel
    /// </summary>
    protected void CreateUserArticleReportList()
    {
        if (nAdminFeatures != 1)
        {
            if (!SupportFeatures(AdminFEATURE.NewsWebSEO))
            {
                Response.Write("没有权限！");
                Response.End();
            }
        }
        int nUserID = Convert.ToInt32(Common.GetQueryValue("userid", 0));
        string strBeginTime = Common.GetQueryValue("begindatetime", 0);
        string strPageTitle = Common.GetQueryValue("pagetitle", 0);
        DateTime dBeginTime = Convert.ToDateTime("1753-01-01 12:00:00");
        DateTime dEndTime = Convert.ToDateTime("9999-12-31 11:59:59");
        if (!string.IsNullOrEmpty(strBeginTime))
        {
            dBeginTime = Convert.ToDateTime(strBeginTime);
            dEndTime = dBeginTime.AddDays(1);

        }
        DataTable dt = new BisNewsWebSEO().GetListAll(nUserID, strPageTitle, dBeginTime, dEndTime);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("sheet");
        //样式1
        Color titlBgColor = Color.FromArgb(255, 128, 128);
        Color titlFontColor = Color.FromArgb(153, 51, 0);
        HSSFFont font = workbook.CreateFont();
        font.FontHeight = 11 * 20;
        font.FontName = "宋体";
        font.Color = GetXLColour(workbook, titlFontColor);
        HSSFCellStyle style = workbook.CreateCellStyle();
        style.SetFont(font);
        style.BorderBottom = CellBorderType.THIN;
        style.BorderLeft = CellBorderType.THIN;
        style.BorderRight = CellBorderType.THIN;
        style.BorderTop = CellBorderType.THIN;
        style.VerticalAlignment = CellVerticalAlignment.CENTER;
        style.Alignment = CellHorizontalAlignment.CENTER;
        style.FillForegroundColor = GetXLColour(workbook, titlBgColor);
        style.FillPattern = CellFillPattern.ALT_BARS;
        style.FillBackgroundColor = GetXLColour(workbook, titlBgColor);

        //样式1
        HSSFFont font1 = workbook.CreateFont();
        font1.FontHeight = 11 * 20;
        font1.FontName = "宋体";
        HSSFCellStyle style1 = workbook.CreateCellStyle();
        style1.SetFont(font1);
        style1.BorderBottom = CellBorderType.THIN;
        style1.BorderLeft = CellBorderType.THIN;
        style1.BorderRight = CellBorderType.THIN;
        style1.BorderTop = CellBorderType.THIN;
        style1.VerticalAlignment = CellVerticalAlignment.CENTER;
        style1.Alignment = CellHorizontalAlignment.LEFT;

        HSSFRow rowTitle = sheet.CreateRow(0);
        rowTitle.Height = 20 * 20;
        HSSFCell cellName = rowTitle.CreateCell(0);
        cellName.CellStyle = style;
        cellName.SetCellValue("标题");
        HSSFCell cellName1 = rowTitle.CreateCell(1);
        cellName1.CellStyle = style;
        cellName1.SetCellValue("网址");
        HSSFCell cellName2 = rowTitle.CreateCell(2);
        cellName2.CellStyle = style;
        cellName2.SetCellValue("用户");
        HSSFCell cellName3 = rowTitle.CreateCell(3);
        cellName3.CellStyle = style;
        cellName3.SetCellValue("创建日期");

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            HSSFRow rowItem = sheet.CreateRow(i + 1);
            rowItem.Height = 20 * 20;
            HSSFCell cellItem = rowItem.CreateCell(0);
            cellItem.CellStyle = style1;
            cellItem.SetCellValue(dt.Rows[i]["PageTitle"].ToString());
            HSSFCell cellItem1 = rowItem.CreateCell(1);
            cellItem1.CellStyle = style1;
            cellItem1.SetCellValue(dt.Rows[i]["PageUrl"].ToString());
            HSSFCell cellItem2 = rowItem.CreateCell(2);
            cellItem2.CellStyle = style1;
            cellItem2.SetCellValue(dt.Rows[i]["Name"].ToString());
            HSSFCell cellItem3 = rowItem.CreateCell(3);
            cellItem3.CellStyle = style1;
            cellItem3.SetCellValue(dt.Rows[i]["CreateTime"].ToString());
        }
        sheet.SetColumnWidth(0, 100 * 256);
        sheet.SetColumnWidth(1, 100 * 256);
        sheet.SetColumnWidth(2, 10 * 256);
        sheet.SetColumnWidth(3, 30 * 256);
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet.Dispose();
            DownExcel(ms, "NewsWeb");
        }

    }
    #endregion


    /// <summary>
    /// 生成自定义颜色
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="SystemColour"></param>
    /// <returns></returns>
    protected short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour)
    {
        short s = 0;
        HSSFPalette XlPalette = workbook.GetCustomPalette();
        HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B);
        if (XlColour == null)
        {
            if (PaletteRecord.STANDARD_PALETTE_SIZE < 255)
            {
                if (PaletteRecord.STANDARD_PALETTE_SIZE < 64)
                {
                    PaletteRecord.STANDARD_PALETTE_SIZE = 64;
                    PaletteRecord.STANDARD_PALETTE_SIZE += 1;
                    XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B);
                }
                else
                {
                    XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B);
                }

                s = XlColour.GetIndex();
            }

        }
        else
            s = XlColour.GetIndex();

        return s;
    }

    /// <summary>
    /// 下载Excel
    /// </summary>
    /// <param name="msExcel"></param>
    /// <param name="strFileName"></param>
    protected void DownExcel(MemoryStream msExcel, string strFileName)
    {
        // 设置编码和附件格式
        Response.ContentType = "application/vnd.ms-excel";
        Response.ContentEncoding = Encoding.UTF8;
        Response.Charset = "";
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName + ".xls", Encoding.UTF8));
        Response.BinaryWrite(msExcel.GetBuffer());
        Response.End();
    }
}